﻿using System.Xml.Linq;
using System.Collections.Generic;
using Entities;
using CommonLib;
using DataAccess.SQL;
using System.Data.SqlClient;
using System.Text;
using System.Data;
using System;
using System.Web;
using Framework.Caching;
using Framework;
using Entities.System;
using Entities.Basic;
using System.Collections.Specialized;

namespace BLL.Basic
{
    public class CustomerInfoBO : BusinessBase
    {
        /// <summary>
        /// WebService接口 
        /// </summary>
        /// <param name="facKey"></param>
        /// <returns></returns>
        public List<Entities.System.Customer> getCustomer()
        {
            string sql = "Select CUSTOMER_KEY, CUSTOMER_NAME from SBRM_CUSTOMER where ACTIVE = 'Y' ORDER BY CUSTOMER_NAME";
            List<Entities.System.Customer> lst = SqlHelper.ExecuteList<Entities.System.Customer>(sql, CommandType.Text);
            return lst;
        }

        /// <summary>获得所有客户信息
        ///
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageNum"></param>
        /// <returns></returns>
        public string getCustomer(Dictionary<string, string> dicLike, Dictionary<string, string> dicEqual, 
            Dictionary<string, string> dicIn, 
            int? pageSize, int? pageNum)
        {
            List<CustomerInfo> lst = new List<CustomerInfo>();
            //if (isAdmin())
            //{
            //    dicIn.Remove("t.FAC_KEY");
            //}

            var condition = getCondition(dicLike, dicEqual, dicIn);
            //if (!dicIn.ContainsKey("t.FAC_KEY") && !isAdmin())
            //{
            //    CacheData myCache = HttpContext.Current.Session["IndexCache"] as CacheData;
            //    string fackeys = string.Empty;
            //    //没任何厂别权限,直接返回空数据
            //    if (myCache.Factories.Count == 0) { return (new Result<CustomerInfo> { rows = lst, total = 0 }).ToJson(); }
            //    //有厂别权限就过滤厂别
            //    myCache.Factories.ForEach(factory => fackeys += factory.FAC_KEY.Value.ToString() + ",");
            //    condition += string.Format(" and t.FAC_KEY in ({0})", fackeys.Substring(0, fackeys.Length - 1));
            //}
            string strSql = string.Empty;
            if (pageSize.HasValue && pageNum.HasValue)
            {
                int from = pageSize.Value * (pageNum.Value - 1);
                int to = pageSize.Value * pageNum.Value;
                string strTableName = @"(  SELECT  t.*, u.USER_ID AS SALES_USER_ID, u.USER_NAME AS SALES_USER_NAME
FROM SBRM_CUSTOMER t 
INNER JOIN SBRM_USER u on t.SALES_USER_KEY = u.USER_KEY 
where t.ACTIVE = 'Y' {0} ) u";
                strTableName = string.Format(strTableName, condition);
                strSql = Common.EasyUIHelper.GetSqlOfPageData2(pageNum, pageSize, strTableName, "CUSTOMER_ID", "SALES_USER_KEY, CUSTOMER_ID", "");
            }
            else
            {
                strSql = string.Format(@"SELECT  *
FROM SBRM_CUSTOMER t 
INNER JOIN SBRM_USER u on t.SALES_USER_KEY = f.USER_KEY where t.ACTIVE = 'Y' {0}                                                   
ORDER BY t.CUSTOMER_ID", condition);
            }
            try
            {
                lst = SqlHelper.ExecuteList<CustomerInfo>(strSql, CommandType.Text);
                //总条数
                strSql = string.Format("select count(*) as rowsCount from SBRM_CUSTOMER t where 1 = 1 {0}", condition);
                object oCount = SqlHelper.ExecuteScalar(strSql, CommandType.Text);
                int total = Convert.ToInt32(oCount);
                return (new Result<CustomerInfo> { rows = lst, total = total }).ToJson();

            }
            catch (Exception ex)
            {
                var error = new Status { Success = false, Message = ex.Message };
                return error.ToJson();

            }

        }

        /// <summary>还原客户信息
        /// 
        /// </summary>
        /// <param name="context"></param>
        /// <returns></returns>
        public string revertCustomer(HttpContext context)
        {
            string sql = string.Empty;
            int result = 0;
            //还原
            if (!string.IsNullOrEmpty(context.Request.Form["id"])) 
                sql = string.Format("update SBRM_CUSTOMER set active = 'Y' where CUSTOMER_ID = '{0}' ", context.Request.Form["id"]);
            result = SqlHelper.ExecuteNonQuery(sql, CommandType.Text);
            if (result > 0) return (new Status { Success = true }).ToJson();
            else return (new Status { Success = false, Message = "执行异常!" }).ToJson();

        }

        /// <summary>获得所有客户信息
        /// 
        /// </summary>
        /// <param name="lisFactory"></param>
        /// <returns></returns>
//        public List<Entities.System.Customer> getAllCustomer(List<Entities.System.Factory> lisFactory)
//        {
//            NameValueCollection nvcFacKey = new NameValueCollection();
//            foreach (Entities.System.Factory _factory in lisFactory)
//            {
//                nvcFacKey.Add("FAC_KEY", _factory.FAC_KEY.ToString());
//            }
//            string strSQL = @"
//                SELECT CUSTOMER_KEY, CUSTOMER_ID, CUSTOMER_NAME
//                FROM SBRM_CUSTOMER
//                ORDER BY CUSTOMER_ID";

//            try
//            {
//                List<Entities.System.Customer> lst = SqlHelper.ExecuteList<Entities.System.Customer>(strSQL, CommandType.Text);
//                return lst;
//            }
//            catch (Exception ex)
//            {
//                return null;
//            }
//        }

        /// <summary>删除客户
        /// 
        /// </summary>
        /// <param name="context"></param>
        /// <returns></returns>
        public string delCustomer(HttpContext context)
        {
            string strSql = string.Empty;
            string Msg = string.Empty;
            bool result = true;
            List<string> sqlArr = new List<string>();
            User user = context.Session["user"] as User;
            CustomerInfo CustomerInfo = JsonHelper.FromJson<CustomerInfo>(context.Request.Form["data"]);
            //删除相关客户代码
            strSql = @"update SBRM_CUSTOMER 
SET ACTIVE = 'N', UPDATE_USER_KEY = @UPDATE_USER_KEY, UPDATE_USER_ID = @UPDATE_USER_ID, 
UPDATE_USER_NAME = @UPDATE_USER_NAME, UPDATE_DATE = GETDATE() 
WHERE CUSTOMER_KEY = @CUSTOMER_KEY ";
            strSql = string.Format(strSql, CustomerInfo.CUSTOMER_KEY);

            using (SqlConnection connection = SqlHelper.Con)
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                SqlTransaction tx = connection.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    //修改
                    cmd.CommandText = strSql;
                    var parms = new SqlParameter[] {
                        new SqlParameter{ ParameterName="@CUSTOMER_KEY", Value= CustomerInfo.CUSTOMER_KEY },
                        new SqlParameter{ ParameterName="@UPDATE_USER_KEY", Value= user.USER_KEY },
                        new SqlParameter{ ParameterName="@UPDATE_USER_ID", Value=user.USER_ID },
                        new SqlParameter{ ParameterName="@UPDATE_USER_NAME", Value=user.USER_NAME }
                    };
                    cmd.Parameters.AddRange(parms);
                    cmd.ExecuteNonQuery();
                    //插入日志
                    insertLog(user, CustomerInfo, "DELETE", tx);
                    tx.Commit();
                }
                catch (SqlException E)
                {
                    result = false;
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }

            if (result) return (new Status { Success = true }).ToJson();
            else return (new Status { Success = false, Message = "执行异常!" }).ToJson();
        }

        /// <summary>新增客户
        /// 
        /// </summary>
        /// <param name="context"></param>
        /// <returns></returns>
        public string addCustomer(HttpContext context)
        {
            string strSql = string.Empty;
            CustomerInfo CustomerInfo;
            CustomerInfo = JsonHelper.FromJson<CustomerInfo>(context.Request.Form["data"]);
            bool result = true;
            //判断是否存在
            strSql = "select active  from SBRM_CUSTOMER where CUSTOMER_ID = '{0}'  ";
            strSql = string.Format(strSql, CustomerInfo.CUSTOMER_ID);
            object oActive = SqlHelper.ExecuteScalar(strSql, CommandType.Text);

            if (oActive != null && oActive != DBNull.Value)
            {
                string isActive = oActive.ToString();
                //判斷重複 
                if (isActive == "Y")
                {
                    throw new Exception("此客户代码已经存在！");
                }
                else throw new Exception("此客户已被删除，是否还原？");
            }

            //新增
            User user = context.Session["user"] as User;
            strSql = @"INSERT INTO SBRM_CUSTOMER
(CUSTOMER_ID,CUSTOMER_NAME,TELEPHONE,ADDRESS,ACTIVE,SALES_USER_KEY,
UPDATE_USER_KEY,UPDATE_USER_ID,UPDATE_USER_NAME,UPDATE_DATE) 
VALUES
(@CUSTOMER_ID,@CUSTOMER_NAME,@TELEPHONE,@ADDRESS,'Y',@SALES_USER_KEY,
@UPDATE_USER_KEY,@UPDATE_USER_ID,@UPDATE_USER_NAME,GETDATE())
select @@identity as ID";

            using (SqlConnection connection = SqlHelper.Con)
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                SqlTransaction tx = connection.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    //插入
                    cmd.CommandText = strSql;
                    var parms = new SqlParameter[] {
                        new SqlParameter{ ParameterName="@CUSTOMER_ID", Value= CustomerInfo.CUSTOMER_ID  },
                        new SqlParameter{ ParameterName="@CUSTOMER_NAME", Value=CustomerInfo.CUSTOMER_NAME   },
                        new SqlParameter{ ParameterName="@SALES_USER_KEY", Value=CustomerInfo.SALES_USER_KEY   },
                        new SqlParameter{ ParameterName="@TELEPHONE", Value=CustomerInfo.TELEPHONE   },
                        new SqlParameter{ ParameterName="@ADDRESS", Value=CustomerInfo.ADDRESS   },
                        new SqlParameter{ ParameterName="@UPDATE_USER_KEY", Value= user.USER_KEY  },
                        new SqlParameter{ ParameterName="@UPDATE_USER_ID", Value=user.USER_ID  },
                        new SqlParameter{ ParameterName="@UPDATE_USER_NAME", Value=user.USER_NAME  }
                    };
                    cmd.Parameters.AddRange(parms);
                    object newID = cmd.ExecuteScalar();
                    CustomerInfo.CUSTOMER_KEY = Convert.ToInt32(newID);
                    //插入日志
                    insertLog(user, CustomerInfo, "ADD", tx);
                    tx.Commit();
                }
                catch (SqlException E)
                {
                    result = false;
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
            if (result) return (new Status { Success = true }).ToJson();
            else return (new Status { Success = false, Message = "执行异常!" }).ToJson();
        }

        /// <summary>编辑客户
        /// 
        /// </summary>
        /// <param name="context"></param>
        /// <returns></returns>
        public string editCustomer(HttpContext context)
        {
            string strSql = string.Empty;
            List<string> sqlArr = new List<string>();
            CustomerInfo CustomerInfo = JsonHelper.FromJson<CustomerInfo>(context.Request.Form["data"]);
            bool result = true;
            //判断是否存在
            strSql = "select active  from SBRM_CUSTOMER where CUSTOMER_KEY <> '{0}' AND CUSTOMER_ID = '{1}'  ";
            strSql = string.Format(strSql, CustomerInfo.CUSTOMER_KEY, CustomerInfo.CUSTOMER_ID);
            object oActive = SqlHelper.ExecuteScalar(strSql, CommandType.Text);

            if (oActive != null && oActive != DBNull.Value)
            {
                string isActive = oActive.ToString();
                //判斷重複 
                if (isActive == "Y")
                {
                    throw new Exception("此客户代码已经存在！");
                }
                else
                {
                    throw new Exception("此客户已被删除，是否还原？");
                }
            }

            //编辑
            User user = context.Session["user"] as User;

            strSql = @"UPDATE SBRM_CUSTOMER SET CUSTOMER_NAME = @CUSTOMER_NAME, SALES_USER_KEY = @SALES_USER_KEY , 
TELEPHONE = @TELEPHONE, ADDRESS = @ADDRESS,
UPDATE_USER_KEY = @UPDATE_USER_KEY, UPDATE_USER_ID = @UPDATE_USER_ID, UPDATE_USER_NAME = @UPDATE_USER_NAME, UPDATE_DATE = GETDATE()  
WHERE  CUSTOMER_KEY = '{0}' ";
            strSql = string.Format(strSql, CustomerInfo.CUSTOMER_KEY);

            using (SqlConnection connection = SqlHelper.Con)
            {
                connection.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = connection;
                SqlTransaction tx = connection.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    //修改
                    cmd.CommandText = strSql;
                    var parms = new SqlParameter[] {
                        new SqlParameter{ ParameterName="@CUSTOMER_NAME", 
                            Value=string.IsNullOrEmpty(CustomerInfo.CUSTOMER_NAME) ? "" : CustomerInfo.CUSTOMER_NAME },
                        new SqlParameter{ ParameterName="@SALES_USER_KEY", Value= CustomerInfo.SALES_USER_KEY },
                        new SqlParameter{ ParameterName="@TELEPHONE", Value= CustomerInfo.TELEPHONE },
                        new SqlParameter{ ParameterName="@ADDRESS", Value= CustomerInfo.ADDRESS },
                        new SqlParameter{ ParameterName="@UPDATE_USER_KEY", Value= user.USER_KEY },
                        new SqlParameter{ ParameterName="@UPDATE_USER_ID", Value=user.USER_ID },
                        new SqlParameter{ ParameterName="@UPDATE_USER_NAME", Value=user.USER_NAME }
                    };
                    cmd.Parameters.AddRange(parms);
                    cmd.ExecuteNonQuery();
                    //插入日志
                    insertLog(user, CustomerInfo, "UPDATE", tx);
                    tx.Commit();
                }
                catch (SqlException E)
                {
                    result = false;
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
                finally
                {
                    cmd.Dispose();
                    connection.Close();
                }
            }
            if (result) return (new Status { Success = true }).ToJson();
            else return (new Status { Success = false, Message = "执行异常!" }).ToJson();

        }

        private int insertLog(User user, CustomerInfo instance, string OperateFlag, SqlTransaction trans)
        {
            string strSql = @"INSERT INTO SBRM_CUSTOMER_LOG
(CUSTOMER_KEY, CUSTOMER_ID, CUSTOMER_NAME, TELEPHONE, ADDRESS,
SALES_USER_KEY, STATUS, UPDATE_USER_KEY, UPDATE_USER_ID, UPDATE_USER_NAME, UPDATE_DATE) 
values
(@CUSTOMER_KEY, @CUSTOMER_ID, @CUSTOMER_NAME, @TELEPHONE, @ADDRESS,
@SALES_USER_KEY, @STATUS, @UPDATE_USER_KEY,@UPDATE_USER_ID, @UPDATE_USER_NAME, GETDATE())";
            if (trans != null) 
                return SqlHelper.ExecuteNonQuery(trans, strSql, CommandType.Text, new SqlParameter[] {
                    new SqlParameter{ ParameterName="@CUSTOMER_KEY" , Value=instance.CUSTOMER_KEY },
                    new SqlParameter{ ParameterName="@CUSTOMER_ID" , Value= instance.CUSTOMER_ID },
                    new SqlParameter{ ParameterName="@CUSTOMER_NAME" , Value=instance.CUSTOMER_NAME },
                    new SqlParameter{ ParameterName="@TELEPHONE", Value= instance.TELEPHONE },
                    new SqlParameter{ ParameterName="@ADDRESS", Value= instance.ADDRESS },
                    new SqlParameter{ ParameterName="@SALES_USER_KEY" , Value=instance.SALES_USER_KEY },
                    new SqlParameter{ ParameterName="@STATUS" , Value=OperateFlag },
                    new SqlParameter{ ParameterName="@UPDATE_USER_KEY" , Value= user.USER_KEY },
                    new SqlParameter{ ParameterName="@UPDATE_USER_ID" , Value=user.USER_ID },
                    new SqlParameter{ ParameterName="@UPDATE_USER_NAME" , Value=user.USER_NAME }
                });
            else
                return SqlHelper.ExecuteNonQuery(strSql, CommandType.Text, new SqlParameter[] {
                    new SqlParameter{ ParameterName="@CUSTOMER_KEY" , Value=instance.CUSTOMER_KEY },
                    new SqlParameter{ ParameterName="@CUSTOMER_ID" , Value= instance.CUSTOMER_ID },
                    new SqlParameter{ ParameterName="@CUSTOMER_NAME" , Value=instance.CUSTOMER_NAME },
                    new SqlParameter{ ParameterName="@TELEPHONE", Value= instance.TELEPHONE },
                    new SqlParameter{ ParameterName="@ADDRESS", Value= instance.ADDRESS },
                    new SqlParameter{ ParameterName="@SALES_USER_KEY" , Value=instance.SALES_USER_KEY },
                    new SqlParameter{ ParameterName="@STATUS" , Value=OperateFlag },
                    new SqlParameter{ ParameterName="@UPDATE_USER_KEY" , Value= user.USER_KEY },
                    new SqlParameter{ ParameterName="@UPDATE_USER_ID" , Value=user.USER_ID },
                    new SqlParameter{ ParameterName="@UPDATE_USER_NAME" , Value=user.USER_NAME }
                });
        }
    }
}
